package com.sec.etech.form.cust.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.sec.etech.sys.util.ContextUtil;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.OrderByElement;
import org.apache.commons.lang3.RandomUtils;
import org.apache.commons.lang3.StringUtils;
import org.openbpm.base.api.exception.BusinessMessage;
import org.openbpm.base.core.util.AppUtil;
import org.openbpm.base.core.util.BeanUtils;
import org.openbpm.base.core.util.PropertyUtil;
import org.openbpm.base.core.util.StringUtil;
import org.openbpm.form.constants.ViewConstants;
import org.openbpm.form.model.custsql.FormCustSql;
import org.openbpm.form.model.custsql.FormCustSqlFields;
import org.openbpm.form.util.DateUtils;
import org.openbpm.form.util.JSONObjectUtil;
import org.openbpm.form.util.TuoMingUtil;
import org.openbpm.form.util.Util;
import org.openbpm.form.vo.*;
import org.openbpm.org.api.model.IGroup;
import org.openbpm.sys.api.model.dto.DataDictDTO;
import org.openbpm.sys.api.service.DataDictService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.openbpm.form.constants.ViewConstants.ViewDataTypeConstants.*;


public class ViewUtil {
    protected static Logger logger = LoggerFactory.getLogger(org.openbpm.form.util.ViewUtil.class);


    public static String getQuerySumSql(String sumSqlColumn, String realSqlColumn, String sql) {
        if (StringUtils.isEmpty(sumSqlColumn)) {
            return "";
        }
        String[] realSqlColumnArr = realSqlColumn.split(", ");
        sql = SqlUtil.optimizeSql(sql, realSqlColumnArr);
        String sumSql = "select " + sumSqlColumn + " from (" + sql + ") B ";
        return sumSql;
    }


    public static String getQueryDataSql(String realSqlColumn, String sql, int curPage, int pageSize) {
        if (curPage <= 0) {
            curPage = 1;
        }

        int offset = (curPage - 1) * pageSize;

        String limit = " limit " + offset + "," + pageSize;

        String dataSql = sql + limit;
        String[] realSqlColumnArr = realSqlColumn.split(", ");
        dataSql = SqlUtil.optimizeSql(dataSql, realSqlColumnArr);

        return dataSql;
    }


    public static SqlConstructVO getSqlConstruct(FormCustSql view, SqlQueryDataVO sqlQueryDataVO) {
        String sql = view.getSqlContent();
        sql = sql.replace("\t", "");

        JSONObject sqlFieldTableAndNameMap = new JSONObject();
        JSONObject sqlFieldExtDictKeyMap = new JSONObject();

        List<FormCustSqlFields> fieldsList = view.getFieldsList();
        for (FormCustSqlFields fieldVO : fieldsList) {
            String name = fieldVO.getName();
            // 18 - 数据字典-扩展
            if(18==fieldVO.getControlType()){
                String dictKey = fieldVO.getDateFormat();
                sqlFieldExtDictKeyMap.put(fieldVO.getFieldName(), dictKey);
            }
            // 表名
            String tableAlias = fieldVO.getTableAlias();
            if (StringUtil.isNotEmpty(tableAlias)) {
                name = tableAlias + "." + name;
            }
            sqlFieldTableAndNameMap.put(fieldVO.getFieldName(), name);
        }


        List<SqlConditionVO> conditionsList = JSONObjectUtil.toList(view.getConditions(), SqlConditionVO.class);
        SqlConstructVO SqlConstructVO = getQuerySql(sql, sqlQueryDataVO, conditionsList, view.getExpand(), sqlFieldTableAndNameMap, sqlFieldExtDictKeyMap);


        SqlConstructVO.setDsAlias(view.getDsKey());

        return SqlConstructVO;
    }


    public static SqlConstructVO getQuerySql(String sql, SqlQueryDataVO sqlQueryDataVO, List<SqlConditionVO> conditions, String expand, JSONObject sqlFieldTableAndNameMap, JSONObject sqlFieldExtDictKeyMap) {
        String orderBy = "";
        String where = "";
        String viewWhere = "";
        String groupBy = "";
        String pureSql = sql;
        if (sqlQueryDataVO != null) {

            sqlQueryDataVO = getQueryDataVo(sqlQueryDataVO, conditions, expand);

            // ?? 如何实现按照代码表的code或name排序？

            orderBy = sqlQueryDataVO.getOrderBy();
            if (StringUtil.isNotEmpty(orderBy)) {
                String[] orderByArr = orderBy.split(",");
                orderBy = " order by ";
                for (String order : orderByArr) {
                    String[] temp = order.split("#");
                    if (BeanUtils.isNotEmpty(temp) && temp.length == 2) {
                        orderBy = orderBy + temp[0] + " " + temp[1] + ",";
                    }
                }
                orderBy = orderBy.substring(0, orderBy.length() - 1);

                //remove order in sql
                int index = sql.toLowerCase().lastIndexOf("order by");
                if(index>0){
                    int n2 = sql.indexOf(")", index);
                    if(n2==-1){
                        sql = sql.substring(0, index-1);
                    }
                }
            }

        } else {

            sqlQueryDataVO = getQueryDataVo(null, conditions, expand);
        }


        SqlConstructVO sqlConstructVO = getWhere(sqlQueryDataVO, sqlFieldTableAndNameMap, sqlFieldExtDictKeyMap);
        where = sqlConstructVO.getWhere();
        viewWhere = sqlConstructVO.getViewWhere();


        groupBy = "";
        if (sqlQueryDataVO.getGroupBy() != null) {
            List<SqGroupByVO> groupByList = sqlQueryDataVO.getGroupBy();
            String tempGroupBy = "";
            for (SqGroupByVO groupByData : groupByList) {
                String groupByTime;
                int groupType = groupByData.getGroupType().intValue();
                String name = groupByData.getName();
                String val = groupByData.getVal();
                if (StringUtil.isEmpty(val) || Util.equals(val, "null")) {
                    continue;
                }
                name = sqlFieldTableAndNameMap.getString(name);
                switch (groupType) {
                    case 1:
                        tempGroupBy = tempGroupBy + name + ",";

                    case 2:
                        groupByTime = "date_format(" + name + ",'" + val + "')";
                        sql = sql.replaceAll(name, groupByTime);
                        tempGroupBy = tempGroupBy + groupByTime + ",";

                    case 3:
                        tempGroupBy = tempGroupBy + val + ",";
                }


            }
            if (!StringUtil.isEmpty(tempGroupBy)) {
                tempGroupBy = tempGroupBy.substring(0, tempGroupBy.length() - 1);
                groupBy = " group by " + tempGroupBy;
            }
        }


        boolean hasWhereExp = SqlUtil.hasWhereExp(sql);

        if (!hasWhereExp && !StringUtil.isEmpty(where)) {
            where = " where 1=1 " + where;
            viewWhere = " where 1=1 " + viewWhere;
        }

        orderBy = StringUtil.ifNullReturnEmpty(orderBy);
        groupBy = StringUtil.ifNullReturnEmpty(groupBy);

        sqlConstructVO.setPureSql(pureSql);
        sqlConstructVO.setGroupBy(groupBy);
        sqlConstructVO.setOrderBy(orderBy);
        sqlConstructVO.setWhere(where);
        sqlConstructVO.setViewWhere(viewWhere);
        sqlConstructVO.setFreemarkerParams(sqlQueryDataVO.getFreemarkerParams());
        sqlConstructVO.setQueryData(sqlQueryDataVO.getQueryData());


        if (StringUtil.isEmpty(where)) {
            //sql = sql.replaceAll(";", "") + groupBy + orderBy;
            if(sql.endsWith(";")){
                sql = sql.substring(0, sql.length()-1);
            }
            sql = sql + groupBy + orderBy;
            sqlConstructVO.setSql(sql);
            return sqlConstructVO;
        }


        // 检查group by
        List<Expression> groupByExp = SqlUtil.getGroupByExps(sql);


        if (!CollectionUtils.isEmpty(groupByExp)) {
            sql = SqlUtil.removeGroupByExp(sql);
        }


        // 检查order by
        List<OrderByElement> orderByExp = SqlUtil.getOrderByExp(sql);

        if (!CollectionUtils.isEmpty(orderByExp)) {
            sql = SqlUtil.removeOrderByExp(sql);
        }

        // try check order by
        int t1 = sql.toLowerCase().indexOf("order by",sql.toLowerCase().lastIndexOf("where"));
        if (t1 > 0) {
            logger.debug("check order by, sql=" + sql);
            int t2 = sql.toLowerCase().indexOf("asc", t1);
            if (t2 > t1) {
                orderBy = sql.substring(t1, t2+3);
                sql = sql.substring(0, t1) + sql.substring(t2 + 3);
            } else {
                t2 = sql.toLowerCase().indexOf("desc", t1);
                if (t2 > t1) {
                    orderBy = sql.substring(t1, t2+4);
                    sql = sql.substring(0, t1) + sql.substring(t2 + 4);
                }else {
                    orderBy = sql.substring(t1);
                    sql = sql.substring(0, t1);
                }
            }
        }
        if(StringUtil.isNotEmpty(orderBy)){
            // order by a.xxx
            if(orderBy.indexOf(".")>1){
                orderBy = " order by " + orderBy.substring(orderBy.indexOf(".")+1);
            }else{
                orderBy = " " +orderBy + " ";
            }
        }


        sql = sql + where + groupBy + orderBy;


        if (StringUtil.isNotEmpty(groupBy)) {
            groupByExp.addAll(SqlUtil.getGroupByExps(sql));
            sql = SqlUtil.removeGroupByExp(sql);
        }
        if (StringUtil.isNotEmpty(orderBy)) {
            orderByExp.clear();  // remove old order by
            orderByExp.addAll(SqlUtil.getOrderByExp(sql));
            sql = SqlUtil.removeOrderByExp(sql);
        }


        sql = SqlUtil.addGroupByExp(sql, groupByExp);


        sql = SqlUtil.addOrderByExp(sql, orderByExp);

        logger.debug(sql);

        sqlConstructVO.setSql(sql);

        return sqlConstructVO;
    }


    public static SqlQueryDataVO getQueryDataVo(SqlQueryDataVO sqlQueryDataVO, List<SqlConditionVO> conditions, String expand) {
        if (sqlQueryDataVO == null) {
            sqlQueryDataVO = new SqlQueryDataVO();
        }


        List<SqlSearchVO> queryData = sqlQueryDataVO.getQueryData();
        if (queryData == null) {
            queryData = new ArrayList<>();
            sqlQueryDataVO.setQueryData(queryData);
        }


        JSONObject queryDataJo = new JSONObject();
        JSONObject groupByJo = new JSONObject();

        for (SqlSearchVO query : queryData) {

            // 检查分组
            if (Util.equals(query.getIsGroup(), Integer.valueOf(1))) {
                groupByJo.put(query.getName(), query.getVal());
                continue;
            }
            queryDataJo.put(query.getName(), query.getVal());
        }


        JSONObject freemarkerParams = new JSONObject();
        if (!CollectionUtils.isEmpty(conditions)) {
            for (SqlConditionVO conditionObj : conditions) {
                if (Util.equals(conditionObj.getParamType(), Integer.valueOf(2))) {
                    String key = conditionObj.getName() + conditionObj.getCntype();
                    freemarkerParams.put(key, conditionObj.getDesc());
                    freemarkerParams.put(conditionObj.getName(), conditionObj.getDesc());
                }
            }
        }


        Iterator<?> iterator = queryData.iterator();
        while (iterator.hasNext()) {
            SqlSearchVO query = (SqlSearchVO) iterator.next();


            if (Util.equals(query.getIsGroup(), Integer.valueOf(1))) {
                iterator.remove();
            }


            String key = query.getName() + query.getCon();
            if (freemarkerParams.containsKey(key)) {
                freemarkerParams.put(key, query.getVal());
                freemarkerParams.put(query.getName(), query.getVal());
                iterator.remove();
            }
        }

        sqlQueryDataVO.setFreemarkerParams(freemarkerParams);


        String groupByConditionStr = JSONObjectUtil.getValByKey("groupby", expand);
        JSONObject groupByConditionNameJo = new JSONObject();
        if (StringUtil.isNotEmpty(groupByConditionStr)) {
            JSONArray groupByConditionJa = JSONArray.parseArray(groupByConditionStr);
            for (Object object : groupByConditionJa) {
                JSONObject groupByConditionJo = (JSONObject) object;
                groupByConditionNameJo.put(groupByConditionJo.getString("name"), groupByConditionJo.getString("type"));
            }
        }

        Map<String, String> paramsData = sqlQueryDataVO.getParamsData();
        if (paramsData == null) {
            paramsData = new HashMap<>();
        }

        if (!CollectionUtils.isEmpty(conditions)) {
            for (SqlConditionVO condition : conditions) {

                String name = condition.getName();
                String desc = condition.getDesc();
                String cntype = condition.getCntype();
                String json = condition.getJson();

                boolean isInit = (condition.getInit().intValue() == 1 || Util.contains(Util.array("is null", "not null"), cntype));
                SqlSearchVO searchVO = new SqlSearchVO();
                searchVO.setName(name);
                searchVO.setCon(cntype);

                String val = condition.getVal();


                if (StringUtil.isNotEmpty(val)) {
                    val = StringUtil.resetJsonStr(resetQuoteStr(val));
                }


                if (paramsData.containsKey(name)) {
                    val = paramsData.get(name);
                }


                if (queryDataJo.containsKey(name) && !isInit) {
                    val = queryDataJo.getString(name);
                }


                Integer dfDataType = condition.getDfDataType();
                if (dfDataType != null) {
                    JSONObject dfValJo = new JSONObject();
                    dfValJo.put("type", dfDataType);
                    dfValJo.put("sdType", "2");
                    dfValJo.put("val", StringUtil.isEmpty(val) ? json : val);
                    val = getDefaultVal(dfValJo);
                }

                searchVO.setVal(val);
                searchVO.setBracket(condition.getBracket());
                searchVO.setAndOr(condition.getAndOr());


                if (!queryDataJo.containsKey(name)) {
                    if (StringUtil.isNotEmpty(val) || isInit) {
                        queryData.add(searchVO);
                        queryDataJo.put(name, val);
                    }

                } else if (StringUtil.isEmpty(val) && !isInit) {
                    iterator = queryData.iterator();
                    while (iterator.hasNext()) {
                        SqlSearchVO query = (SqlSearchVO) iterator.next();
                        if (Util.equals(query.getName(), name)) {
                            iterator.remove();


                            break;
                        }
                    }
                }

                if (!isInit && Util.equals(condition.getIsMust(), Integer.valueOf(1)) &&
                        !queryDataJo.containsKey(name)) {
                    String errorMsg = desc + "参数不能为空";
                    logger.error(errorMsg);
                    throw new BusinessMessage(errorMsg);
                }
            }
        }

        return sqlQueryDataVO;
    }


    public static String getDefaultVal(JSONObject dfVal) {
        IGroup currentGroup;
        int maxInt, minInt;
        String val = "";
        String dfType = dfVal.getString("type");
        String dfV = "";
        if (dfVal.containsKey("val")) {
            dfV = dfVal.getString("val");
        }
        switch (dfType) {
            case DATA_NOMAL:  //"1":
                dfV = dfV.replaceAll("\\$\\{currentUserId\\}",
                        StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId()));
                dfV = dfV.replaceAll("\\$\\{currentUserName\\}", ContextUtil.getCurrentUserName());
                dfV = dfV.replaceAll("\\$\\{currentOrgId\\}", ContextUtil.getCurrentGroupId());
                dfV = dfV.replaceAll("\\$\\{curDate\\}", DateUtils.getCurrentDate());
                dfV = dfV.replaceAll("\\$\\{curTime\\}", DateUtils.getCurrentDate("HH:mm:ss"));
                dfV = dfV.replaceAll("\\$\\{curDateTime\\}", DateUtils.getCurrentTime());

                val = dfV;
                break;
            case DATA_CUR_DATE:  //"2":
                val = DateUtils.formatDate(new Date(), "" + (StringUtil.isNotEmpty(dfV) ? dfV : "yyyy-MM-dd HH:mm:ss"));
                break;
            case DATA_CUR_USER_ID: //"3":
                val = StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId());
                break;
            case DATA_CUR_USER_NAME: //"4":
                val = ContextUtil.getCurrentUserName();
                break;
            case DATA_SYS_LSH: //"5":
                val = ContextUtil.getCurrentUserName();
                break;
            case DATA_RANDOM_NUM: //"6":
                minInt = 0;
                maxInt = 1000;
                if (!StringUtil.isEmpty(dfV)) {
                    if (dfV.indexOf(",") > 0) {
                        String[] split = dfV.split(",");
                        minInt = Integer.parseInt(split[0]);
                        maxInt = Integer.parseInt(split[1]);
                    } else {
                        maxInt = Integer.parseInt(dfV);
                    }
                }
                val = RandomUtils.nextInt(minInt, maxInt) + "";
                break;
            case DATA_CUR_USER_DEPT_ID: //"7":
                val = ContextUtil.getCurrentGroupId();
                break;
            case DATA_CUR_USER_DEPT_NAME: //"8":
                currentGroup = ContextUtil.getCurrentGroup();
                if (currentGroup != null) {
                    val = currentGroup.getGroupName();
                    break;
                }
                val = "";
                break;

            case DATA_CUR_MAIN_TABLE_ID://"12":
                val = "${mainTableId}";
                break;
            case ENV: // "15":
                val = PropertyUtil.getProperty("spring.profiles.active");
                break;
        }


        return val;
    }


    public static String resetQuoteStr(String str) {
        if (StringUtils.isEmpty(str)) {
            return "";
        }

        str = str.replaceAll("#___#", "'").replaceAll("#_#_#", "\"").replaceAll("#-#-#", "\\\\");
        return str;
    }


    public static String replaceParams(String sql) {
        sql = sql.replaceAll("\\$\\{currentUserId\\}",
                StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentUserId()));
        sql = sql.replaceAll("\\$\\{currentOrgId\\}",
                StringUtil.ifNullReturnEmpty(ContextUtil.getCurrentGroupId()));

        sql = sql.replaceAll("\\$\\{currentUserName\\}", ContextUtil.getCurrentUserName());
        // 当前部门以及子部门
        if(sql.indexOf("${currentGroupCodes}")>0){
            sql = sql.replaceAll("\\$\\{currentGroupCodes\\}", convetToInStr(ContextUtil.getCurrentGroupCodes()));
        }
        if(sql.indexOf("${currentGroupIds}")>0){
            sql = sql.replaceAll("\\$\\{currentGroupIds\\}", convetToInStr(ContextUtil.getCurrentGroupIds()));
        }
        // 作为主管/助理/内勤的部门以及子部门 id
        if(sql.indexOf("${currentMasterGroupIds}")>0){
            sql = sql.replaceAll("\\$\\{currentMasterGroupIds\\}", convetToInStr(ContextUtil.getCurrentMasterGroupIds()));
        }
        // 作为主管/助理的部门以及子部门 id（不含内勤）
        if (sql.indexOf("${currentMasterGroupIds2}") > 0) {
            sql = sql.replaceAll("\\$\\{currentMasterGroupIds2\\}", convetToInStr(ContextUtil.getCurrentMasterGroupIds2()));
        }
        if (sql.indexOf("${currentMasterGroupName}") > 0) {
            sql = sql.replaceAll("\\$\\{currentMasterGroupName\\}", ContextUtil.getCurrentMasterGroupName());
        }
        // TWSAP报表显示+内勤
        if (sql.indexOf("${currentAttendenceIds}") > 0) {
            sql = sql.replaceAll("\\$\\{currentAttendenceIds\\}", convetToInStr(ContextUtil.getCurrentAttendenceIds()));
        }
        // TWSAP报表显示
        if (sql.indexOf("${currentStatIds}") > 0) {
            sql = sql.replaceAll("\\$\\{currentStatIds\\}", convetToInStr(ContextUtil.getCurrentStatIds()));
        }

        sql = sql.replaceAll("\\$\\{curUserPermCodes\\}", convetToInStr(ContextUtil.getCurrentUserRight()));
        sql = sql.replaceAll("\\$\\{curDate\\}", DateUtils.getCurrentDate());
        sql = sql.replaceAll("\\$\\{curTime\\}", DateUtils.getCurrentDate("HH:mm:ss"));
        sql = sql.replaceAll("\\$\\{curDateTime\\}", DateUtils.getCurrentTime());
        sql = sql.replaceAll("\\$\\{env\\}", PropertyUtil.getProperty("spring.profiles.active"));


        return sql;
    }

    // 重构，用于自定义 ContextUtil 各项参数
    // 不推荐使用，仅用于多线程环境无法获取 ContextUtil 时的替代方案
    public static String replaceParams(String sql, Map<String, Object> contextMap) {
        String currentUserId = (String) contextMap.get("currentUserId");
        String currentGroupId = (String) contextMap.get("currentGroupId");
        String currentUserName = (String) contextMap.get("currentUserName");
        String[] currentGroupCodes = (String[]) contextMap.get("currentGroupCodes");
        String[] currentGroupIds = (String[]) contextMap.get("currentGroupIds");
        String[] currentMasterGroupIds = (String[]) contextMap.get("currentMasterGroupIds");
        String[] currentMasterGroupIds2 = (String[]) contextMap.get("currentMasterGroupIds2");
        String[] currentAttendenceIds = (String[]) contextMap.get("currentAttendenceIds");
        String[] currentStatIds = (String[]) contextMap.get("currentStatIds");

        String currentMasterGroupName = (String) contextMap.get("currentMasterGroupName");
        Set<String> currentUserRight = (Set<String>) contextMap.get("currentUserRight");
        String currentDate = (String) contextMap.get("currentDate");
        String currentTime = (String) contextMap.get("currentTime");
        String currentDate1 = (String) contextMap.get("currentDate1");
        String property = (String) contextMap.get("property");

        sql = sql.replaceAll("\\$\\{currentUserId\\}", StringUtil.ifNullReturnEmpty(currentUserId));
        sql = sql.replaceAll("\\$\\{currentOrgId\\}", StringUtil.ifNullReturnEmpty(currentGroupId));
        sql = sql.replaceAll("\\$\\{currentUserName\\}", currentUserName);
        // 当前部门以及子部门
        if (sql.indexOf("${currentGroupCodes}") > 0) {
            sql = sql.replaceAll("\\$\\{currentGroupCodes\\}", convetToInStr(currentGroupCodes));
        }
        if (sql.indexOf("${currentGroupIds}") > 0) {
            sql = sql.replaceAll("\\$\\{currentGroupIds\\}", convetToInStr(currentGroupIds));
        }
        // 首页待办+内勤
        if (sql.indexOf("${currentMasterGroupIds}") > 0) {
            sql = sql.replaceAll("\\$\\{currentMasterGroupIds\\}", convetToInStr(currentMasterGroupIds));
        }
        // 首页待办显示
        if (sql.indexOf("${currentMasterGroupIds2}") > 0) {
            sql = sql.replaceAll("\\$\\{currentMasterGroupIds2\\}", convetToInStr(currentMasterGroupIds2));
        }
        if (sql.indexOf("${currentMasterGroupName}") > 0) {
            sql = sql.replaceAll("\\$\\{currentMasterGroupName\\}", currentMasterGroupName);
        }
        // TWSAP报表显示+内勤
        if (sql.indexOf("${currentAttendenceIds}") > 0) {
            sql = sql.replaceAll("\\$\\{currentAttendenceIds\\}", convetToInStr(currentAttendenceIds));
        }
        // TWSAP报表显示
        if (sql.indexOf("${currentStatIds}") > 0) {
            sql = sql.replaceAll("\\$\\{currentStatIds\\}", convetToInStr(currentStatIds));
        }
        sql = sql.replaceAll("\\$\\{curUserPermCodes\\}", convetToInStr(currentUserRight));
        sql = sql.replaceAll("\\$\\{curDate\\}", currentDate);
        sql = sql.replaceAll("\\$\\{curTime\\}", currentDate1);
        sql = sql.replaceAll("\\$\\{curDateTime\\}", currentTime);
        sql = sql.replaceAll("\\$\\{env\\}", property);


        return sql;
    }

    private static String convetToInStr(Collection<String> values) {
        return convetToInStr(values.toArray(new String[values.size()]));
    }

    private static String convetToInStr(String[] values) {
        return StringUtil.join(values, "','");
    }


    public static SqlConstructVO getWhere(SqlQueryDataVO sqlQueryDataVO, JSONObject sqlFieldTableAndNameMap, JSONObject sqlFieldExtDictKeyMap) {
        SqlConstructVO SqlConstructVO = new SqlConstructVO();
        SqlConstructVO.setWhere("");
        SqlConstructVO.setViewWhere("");

        SqlConstructVO.setBackupLogId(sqlQueryDataVO.getBackupLogId());

        List<SqlSearchVO> queryData = sqlQueryDataVO.getQueryData();
        if (CollectionUtils.isEmpty(queryData)) {
            return SqlConstructVO;
        }

        StringBuilder whereSb = new StringBuilder();
        StringBuilder viewWhereSb = new StringBuilder();

        Map<String, Object> paramsMap = new HashMap<>();
        for (SqlSearchVO vo : queryData) {
            String name = vo.getName();
            if (!sqlFieldTableAndNameMap.containsKey(name)) {
                name = StringUtil.underlineToCamel(name.toLowerCase());
            }
            String searchName = sqlFieldTableAndNameMap.getString(name);

            // 检查后端代码表查询
            String dictKey = null;
            if(sqlFieldExtDictKeyMap.containsKey(name)){
                dictKey = sqlFieldExtDictKeyMap.getString(name);
            }

            // 修改必须配置条件字段才能搜索的规则
            if (StringUtil.isEmpty(searchName)) {
                searchName = name;
                //continue;
            }
            vo.setName(searchName);
            whereSb.append(getWhereByCon(vo, dictKey));

            vo.setName(name);
            viewWhereSb.append(getWhereByCon(vo, dictKey));

            String val = vo.getVal();
            paramsMap.put(name, val);

            if (DateUtils.isDate(val)) {
                if (vo.getCon().indexOf(">") >= 0) {
                    paramsMap.put(name + "Start", val);
                }
                if (vo.getCon().indexOf("<") >= 0) {
                    paramsMap.put(name + "End", val);
                }
            }
        }

        SqlConstructVO.setParamsMap(paramsMap);

        if (whereSb.length() > 0) {
            SqlConstructVO.setWhere(" " + whereSb.toString());
            SqlConstructVO.setViewWhere(" " + viewWhereSb.toString());
        }

        return SqlConstructVO;
    }



    public static String getWhereByCon(SqlSearchVO searchVO, String dictKey) {

        int i;
        String rlike;
        String llike;
        String[] vals;
        String con = searchVO.getCon();
        String val = searchVO.getVal();
        String val2 = searchVO.getVal2();
        String name = searchVO.getName();

        if (name.indexOf(".") > 0) {
            String[] tempName = name.split("\\.");
            name = " " + tempName[0] + ".`" + tempName[1] + "` ";
        } else {
            name = " `" + name + "` ";
        }

        String sql = "";

        if (StringUtils.isEmpty(val) || val.equals("null")) {
            val = "";
        }
        if (StringUtils.isEmpty(val2) || val2.equals("null")) {
            val2 = "";
        }


        String bracket = searchVO.getBracket();
        String andOr = searchVO.getAndOr();


        if ("(".equals(bracket)) {
            andOr = andOr + " " + bracket + " ";
        }

        switch (con) {
            case "=":
            case "<>":
                // FYQ 如果是日期，使用 DATE 忽略时分秒后判等
                if (DateUtils.isDate(val)) {
                    // fix mysql 性能优化（增加该日期字段索引即可，无需修改代码）, 2021/01/09
                    sql = andOr +" DATE("+ name+") " + con + " '" + val + "' ";
//                    if("=".equals(con)){
//                        sql = andOr +" "+ name+"  between '" + val + " 00:00:00' and '" + val + " 23:59:59' ";
//                    }else{
//                        sql = andOr +" not ("+ name+"  between '" + val + " 00:00:00' and '" + val + " 23:59:59') ";
//                    }
                    break;
                }

                if(StringUtil.isNotEmpty(dictKey)){
                    String dictValue = getEqValueFromDict(dictKey, val);
                    // fix etech项目字段字段只搜索label 2021/01/12
                    if(StringUtil.isNotEmpty(dictValue)){
                        if("=".equals(con)){
                            //sql = andOr + "(" + name +  " = '" + val + "' or "+name+" = '"+dictValue+"')";
                            sql = andOr + " "+name+" = '"+dictValue+"' ";
                        }else{ //<>
                            //sql = andOr + "(" + name +  " <> '" + val + "' and "+name+" <> '"+dictValue+"')";
                            sql = andOr + " "+name+" <> '"+dictValue+"' ";
                        }
                        break;
                    }else{
                        if("=".equals(con)){
                            sql = andOr + " 1=2 ";
                        }
                        break;
                    }
                }

                if (StringUtils.isNumeric(val)) {
                    sql = andOr + name + con + " " + val + " ";
                    break;
                }

                sql = andOr + name + con + " '" + val + "' ";
                break;
            case ">=":
            case ">":
            case "<":
            case "<=":
                if (StringUtils.isNumeric(val)) {
                    sql = andOr + name + con + " " + val + " ";
                    break;
                }
                sql = andOr + name + con + " '" + val + "' ";
                break;
            case "llike":
            case "rlike":
            case "like":
            case "not like":
                String sql2 = "";
                vals = val.split(",");
                llike = "%";
                rlike = "%";
                if (con.equals("llike")) {
                    rlike = "";
                }
                if (con.equals("rlike")) {
                    llike = "";
                }
                if (vals.length > 1) {
                    sql2 += "(";
                }
                for (i = 0; i < vals.length; i++) {
                    if ("not like".equalsIgnoreCase(con)) {
                        sql2 = sql2 + name + " not like '" + llike + vals[i] + rlike + "' ";
                        if (i != vals.length - 1) {
                            sql2 = sql2 + " and ";
                        }
                    } else {
                        sql2 = sql2 + name + " like '" + llike + vals[i] + rlike + "' ";
                        if (i != vals.length - 1) {
                            sql2 = sql2 + " or ";
                        }
                    }
                }
                if (vals.length > 1) {
                    sql2 += ")";
                }
                if(StringUtil.isNotEmpty(dictKey)) {
                    String[] dictValues = getLikeValuesFromDict(dictKey, val, con);
                    // fix etech项目字段字段只搜索label 2021/01/12
                    if(StringUtil.isNotEmpty(dictValues)){
                        if ("not like".equalsIgnoreCase(con)){
                            //sql2 = "(" + sql2 + " and " + name +" not in ('"+StringUtil.join(dictValues, "','")+"'))";
                            sql2 = " " + name +" not in ('"+StringUtil.join(dictValues, "','")+"')";
                        }else {
                            //sql2 = "(" + sql2 + " or " + name +" in ('"+StringUtil.join(dictValues, "','")+"'))";
                            sql2 = " " + name +" in ('"+StringUtil.join(dictValues, "','")+"')";
                        }
                    }else{
                        // dictValues is null
                        if ("not like".equalsIgnoreCase(con)){
                            //
                            break;
                        }else{
                            sql2 = " 1=2 ";
                        }
                    }
                }
                // 定制支持TODO任务搜索
                if("task.`task_id_`".equalsIgnoreCase(name.trim())){
                    if ("not like".equalsIgnoreCase(con)){
                        sql2 = "("+sql2+" and "+ sql2.replaceAll("task.`task_id_`","task.`name_`") +" )";
                    }else {
                        sql2 = "("+sql2+" or "+ sql2.replaceAll("task.`task_id_`","task.`name_`") +" )";
                    }

                }
                // 针对流程任务特别处理  bpmExt=bpm_flow_ext
                if("`bpm_inst_id`".equalsIgnoreCase(name.trim()) || name.trim().endsWith(".`bpminstanceid`") || name.trim().endsWith(".`bpm_instance_id`")){
                    //inst_id_,node_id_,name_
                    String sql3  = "select distinct inst_id_ from bpm_task t where " + sql2.replaceAll(name, "t.node_id_") + " or "+ sql2.replaceAll(name, "t.name_");
                    // 增加"已经结束"的特别逻辑判断 2021/2/19
                    boolean checkCompleted = false;
                    if(sql2.indexOf("已经结束")>0 || sql2.indexOf("已经完成")>0){
                        checkCompleted = true;
                    }
                    // fix 性能优化去掉原来条件sql2, 2021/01/09
                    if ("not like".equalsIgnoreCase(con)){
                        //sql2 = "(" + sql2 + " and " + name +" not in ("+sql3+"))";
                        sql2 = "( " + name +" not in ("+sql3+"))";
                    }else{
                        //sql2 = "(" + sql2 + " or " + name +" in ("+sql3+"))";
                        sql2 = "(" + name +" in ("+sql3+"))";
                        if(checkCompleted){
                            sql2 = "(" + name +" not in (select distinct inst_id_ from bpm_task t ))";
                        }
                    }
                }
                sql = andOr + " " + sql2;
                break;
            case "in":
            case "not in":
                if (StringUtils.isNotEmpty(val)) {
                    vals = val.split(",");
                    val = "";
                    for (String v : vals) {
                        if (StringUtils.isNumeric(v)) {
                            val = val + v + ",";
                        } else {
                            val = val + "'" + v + "',";
                        }
                    }
                    val = val.substring(0, val.length() - 1);

                    sql = andOr + " " + name + " " + con + " (" + val + ") ";

                    if ("in".equals(con) && val.indexOf("'void'") > -1  ){
                        val = val.replace("'void'", " 0 ");
                        sql = andOr + "( " + name + " " + con + " (" + val + ") OR "+name+" IS NULL OR TRIM( "+name+" ) = '' ) ";
                    }

                }else {
                    // 空值处理
                    if("not in".equals(con)){
                        sql = andOr + " (" + name + " is not null and " + name + " <> 0 and "+name+" <>'0' and "+name+" <> '' and "+name+" <> 'null' ) ";
                    }else {
                        sql = andOr + " (" + name + " is null or " + name + " = 0 or " + name + " = '0' or "+name+" = '' or "+name+" = 'null' ) ";
                    }
                }

                break;

            case "is null":
                if (Util.equals(val, Integer.valueOf(1))) {
                    sql = andOr + " (" + name + " is null or " + name + " = '')";
                    break;
                }
                sql = andOr + " (" + name + " is not null and " + name + " != '')";
                break;

            case "not null":
                if (Util.equals(val, Integer.valueOf(0))) {
                    sql = andOr + " (" + name + " is null or " + name + " = '')";
                    break;
                }
                sql = andOr + " (" + name + " is not null and " + name + " != '')";
                break;

            case "between":
                if ("".equals(val2)) {
                    sql = andOr + name + " >='" + val + "' ";
                } else if ("".equals(val)) {
                    sql = andOr + name + " <='" + val2 + "' ";
                } else {
                    sql = andOr + name + " between '" + val + "' and '" + val2 + "' ";
                }
                break;
        }


        if (")".equals(bracket)) {
            sql = sql + bracket;
        }

        return sql;
    }

    public static String getEqValueFromDict(String dictKey, String input){
        if(dictKey==null || input==null){
            return null;
        }
        List<DataDictDTO>  dictList = getDictNodeList(dictKey);
        if(dictList!=null){
            for(DataDictDTO dataDict: dictList){
                if(input.equalsIgnoreCase(dataDict.getName())){
                    return dataDict.getKey();
                }
            }
        }
        return null;
    }

    public static String[] getLikeValuesFromDict(String dictKey, String input, String con){
        if(dictKey==null || input==null){
            return null;
        }
        List<String> values = new ArrayList<>();
        List<DataDictDTO> dictList = getDictNodeList(dictKey);
        if(dictList==null || dictList.isEmpty()){
            return null;
        }
        input = input.toLowerCase();
        for(DataDictDTO dataDict: dictList){
            if("llike".equalsIgnoreCase(con)){
                if(dataDict.getName().toLowerCase().startsWith(input)){
                    values.add(dataDict.getKey());
                }
            }else  if("rlike".equalsIgnoreCase(con)){
                if(dataDict.getName().toLowerCase().endsWith(input)){
                    values.add(dataDict.getKey());
                }
            }else {
                if(dataDict.getName().toLowerCase().indexOf(input)>=0){
                    values.add(dataDict.getKey());
                }
            }
        }
        if(!values.isEmpty()){
            return values.toArray(new String[values.size()]);
        }
        return null;
    }

    private static List<DataDictDTO> getDictNodeList(String dictKey){
        DataDictService dataDictService =(DataDictService)AppUtil.getBean("etechDictService");
        List<DataDictDTO>  dictList = dataDictService.getDictNodeList(dictKey, false);
        return  dictList;
    }


    public static SqlViewVO getFieldColumnJo(List<FormCustSqlFields> commonFieldList, JSONObject expandJo) {
        String realSqlColumn = "";

        Map<String, Object> sqlJo = new HashMap<>();

        String sumSqlColumn = "";
        for (FormCustSqlFields field : commonFieldList) {
            String realSearchSqlColumn = getRealSearchSqlColumn(field);
            if(!"".equals(realSqlColumn)){
                realSqlColumn += ",";
            }
            realSqlColumn += realSearchSqlColumn;
            // 检查sum, 支持统计求和字段
            // expand中包含"needSum":"1"
            // 只有数字number字段"number".equals(field.getDataType())
            if(expandJo!=null && expandJo.containsKey("needSum") && "1".equals(expandJo.getString("needSum"))
                    && StringUtil.isNotEmpty(field.getExpand()) && field.getExpand().indexOf("\"needSum\":\"1\"")>0){
                if(!"".equals(sumSqlColumn)){
                    sumSqlColumn += ",";
                }
                sumSqlColumn += realSearchSqlColumn;
            }
        }

        sqlJo.put("realSqlColumn", realSqlColumn);
        sqlJo.put("sumSqlColumn", sumSqlColumn);

        SqlViewVO sqlViewVO = new SqlViewVO();
        sqlViewVO.setRealSqlColumn(realSqlColumn);
        sqlViewVO.setSumSqlColumn(sumSqlColumn);

        return sqlViewVO;
    }


    public static String getRealSearchSqlColumn(FormCustSqlFields field) {
        String realSqlColumn = "";
        if (BeanUtils.isEmpty(field)) {
            return "";
        }
        String fieldName = field.getFieldName();

        if (field.getDataType().equals("date")) {
            realSqlColumn = " date_format(" + fieldName + ",'%Y-%c-%d %H:%i:%s') as `" + fieldName + "`";
        } else {
            realSqlColumn = " `" + fieldName + "`";
        }
        return realSqlColumn;
    }


    public static String changeQueryListToJSONStr(List<Map<String, Object>> queryForList) {
        JSONArray list = JSONArray.parseArray(JSONArray.toJSONString(queryForList));

        String listStr = list.toString();


        listStr = listStr.replaceAll(":null", ":\"\"");

        // 2021-07-08 删除路径替换
        // listStr = listStr.replaceAll("\\\\\\\\", "/");

        return listStr;
    }


    public static Object handleData(String code, String list, JdbcTemplate newJdbcTemplate, SqlViewVO sqlViewVO) {
        List<FormCustSqlFields> commonFieldList = sqlViewVO.getFieldList();
        logger.debug("处理查询数据之后的一些特殊数据,code:{}", code);


        // 处理敏感字段
        try {
            JSONArray listData = JSONArray.parseArray(list);
            if (!listData.isEmpty()) {
                TuoMingUtil.tuoMing(commonFieldList, listData);
            }
            return listData;
        } catch (Exception e) {
            logger.error("处理查询数据之后的一些特殊数据-数据转json异常,code:{},e:{}", code, e);
            return list;
        }
    }


    public static List<FormCustSqlFields> getFields(List<FormCustSqlFields> settings, String type) {
        List<FormCustSqlFields> list = new ArrayList<>();
        for (FormCustSqlFields vo : settings) {
            switch (type) {

                case "1":
                    if (ViewConstants.FieldsOperTypeCode.OPERABLE.contains(vo.getAddAble())) {
                        list.add(vo);
                    }

                case "2":
                case "3":
                    if (ViewConstants.FieldsOperTypeCode.OPERABLE.contains(vo.getEditAble())) {
                        list.add(vo);
                    }

                case "5":
                    if (vo.getExportAble().intValue() == 1) {
                        list.add(vo);
                    }
            }


        }
        return list;
    }

    private static Map<String, String> mapCon = new HashMap();
    static{
        mapCon.put("equals","=");
        mapCon.put("notEqual","<>");
        mapCon.put("contains","like");
        mapCon.put("notContains","not like");
        mapCon.put("startsWith","rlike");
        mapCon.put("endsWith","llike");
        mapCon.put("lessThan","<");
        mapCon.put("lessThanOrEqual","<=");
        mapCon.put("greaterThan",">");
        mapCon.put("greaterThanOrEqual",">=");
        mapCon.put("inRange","between");
        // for ag-grid
        // equals/notEqual   （text/number/date)
        // contains/notContains/startsWith/endsWith (text)
        // lessThan/lessThanOrEqual/greaterThan/greaterThanOrEqual  (number/date)
        // inRange (number, date)
    }
    public static String convertCon(String con){
        if(mapCon.containsKey(con)){
            return  mapCon.get(con);
        }
        return con;
    }


    public static Map<String, String> getUrlParams(String queryData) {
        ObjectMapper mapper = new ObjectMapper();
        Map<String, String> urlParams = new HashMap<>();
        try {
            urlParams = (Map<String, String>) mapper.readValue(queryData, Map.class);
        } catch (Exception exception) {
        }

        if (urlParams != null) {

            urlParams.remove("queryData");
            urlParams.remove("groupBy");
            urlParams.remove("paramsData");
            urlParams.remove("freemarkerParams");
        }
        return urlParams;
    }


    public static boolean isDate(String str) {
        boolean convertSuccess= true;
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        try {
            format.setLenient(false);
            format.parse(str);
        } catch (ParseException e) {
            convertSuccess=false;
        }
        return convertSuccess;
    }


}
